Executing a Query in Forms 10g Fails With Frm-41838 Unable To Open Temporary Record File

A query executed in a Forms application fails giving the following error:

FRM-41838 Unable to open temporary record file

Cause

Oracle Forms tries to create a temporary file if a query is issued and the number of records returned exceeds the block property 'Records Buffered' value. When using OracleAS Forms Services, the temporary file will be written by default to the directory specified for the TMP variable in:

$ORACLE_HOME/opmn/conf/opmn.xml.

If this directory does not exist, has insufficient space or has restricted read / write privileges the FRM-41838 error can occur.
Solution

Solution A:

1. Edit the Forms Listener Servlet env file. This will be the file referenced by the formsweb.cfg applet parameter envFile. By default, envFile = default.env, default.env is located in $ORACLE_HOME/forms90/server or in $ORACLE_HOME/forms/server

2. Add the line e.g.

TMP=/tmp

(assuming that the directory specified e.g. /tmp has sufficient space and read/write privileges)

3. Stop and restart the OC4J_BI_Forms component for the change to take effect

Solution B:

Change the value for the TMP variable setting in opmn.xml by following all the steps below:

1. Edit $ORACLE_HOME/opmn/conf/opmn.xml where $ORACLE_HOME corresponds to the Oracle Application Server middle tier home (BI & Forms)

2. Look for the line e.g. on Unix

3. Check that the directory specified exists, has sufficient space and read/write privileges. If necessary, change the TMP value e.g.

4. If the directory has been changed in opmn.xml, stop and restart all the middle tier components for the change to take effect.

Source : Oracle Metalink Doc ID: 304004.1

Compiling all the Forms and Reports modules residing in a directory

For Windows

Forms/Reports 6i

REM WINDOWS COMPILE FORMS 
::compile_forms.bat 
cls 
Echo compiling Forms....
for %%f IN (*.fmb) do ifcmp60 userid=scott/tiger@v817  module=%%f batch=yes 
    module_type=form compile_all=yes window_state=minimize 
ECHO FINISHED COMPILING 

REM WINDOWS COMPILE REPORT 
::compile_report.bat 
cls 
Echo compiling Report .....
for %%f IN (*.rdf) do RWCON60 userid=scott/tiger@v817 batch=yes source=%%f 
    stype=rdffile DTYPE=REPFILE  OVERWRITE=yes  logfile=log.txt
ECHO FINISHED COMPILING
Forms/Reports 9.0.X

REM WINDOWS COMPILE FORMS 
::compile_forms.bat 
cls 
Echo compiling Forms....
for %%f IN (*.fmb) do ifcmp90 userid=scott/tiger@v817  module=%%f batch=yes 
    module_type=form compile_all=yes window_state=minimize 
ECHO FINISHED COMPILING 

REM WINDOWS COMPILE REPORT 
::compile_report.bat 
cls 
Echo compiling Report .....
for %%f IN (*.rdf) do rwconverter userid=scott/tiger@v817 batch=yes 
    source=%%f stype=rdffile DTYPE=REPFILE  OVERWRITE=yes  logfile=log.txt
ECHO FINISHED COMPILING
For UNIX

Forms/Reports 6i

#UNIX Forms Compile 
#compile_forms.sh 
for i in `ls *.fmb`
do
echo Compiling Form $i ....
f60genm userid=scott/tiger@bs817 batch=yes module=$i module_type=form 
    compile_all=yes window_state=minimize
done

#UNIX COMPILE REPORTS
#compile_rep.sh
for i  in `ls  *.rdf`
do
echo Compiling Report $i  ...
rwcon60 userid=scott/tiger@bs817 batch=yes source=$i  stype=rdffile 
    dtype=repfile overwrite=yes
done
Forms/Reports 9.0.X

#UNIX Forms Compile 
#compile_forms.sh 
for i in `ls *.fmb`
do
echo Compiling Form $i ....
f90genm userid=scott/tiger@bs817 batch=yes module=$i module_type=form 
    compile_all=yes window_state=minimize
done

( For 10g = forms 9.0.4.x, you can use f90gen also)

#UNIX COMPILE REPORTS
#compile_rep.sh
for i  in `ls  *.rdf`
do
echo Compiling Report $i  ...
rwconverter.sh userid=scott/tiger@bs817 batch=yes source=$i  
    stype=rdffile dtype=repfile overwrite=yes
done

Enabling Tracing and Session Information with 10gR2 Enterprise Manager and Forms

ENABLING SESSION DETAILS AND TRACE LOGGING
-------------------------------------------
By default the formsweb.cfg file is set up with session detail and trace 
logging disabled. In order to enable these features, the formsweb.cfg file in 
the ORACLE_HOME/forms90/server ( ORACLE_HOME/forms/server in 10gR2) directory 
must be modified and the middle-tier restarted.


Change the Configuration File
-----------------------------
For session details: 

1) Modify the em_mode parameter to: 
   em_mode=1

For trace logging:

1) Modify the following parameters:
   allow_debug=true

2) If the allowURLConnections parameter appears in formsweb.cfg, ensure that
   the parameter is set to: 
   allowURLConnections=true

   The default for allowURLConnections is true, so if it does not appear in the
   formsweb.cfg file, no modifications are required for this parameter.

3) Run a form

4)  From  EM -> Forms Link -> User Sessions

5)  Select Trace Group -> debug

6)  Select a session.

7)  Click the 'Trace ON' button.

8)  Click the 'View Trace Log' Icon to view it.

9.) Now a Login request comes up. 
    (The documentation does not clearly explain 
    to which "administrators" group is being referenced.  Most would assume the
    EM administrator would have access. This is not true. The "administrators"
    group referenced in the documentation relates to JAZN security and not the
    OS or EM.)

    A default "administrators" group is created with a default user and 
    password.  The default username and password is as follows:
    username = admin
    password = welcome

To change the default or create your own users, refer to the 
Oracle Application Server Containers for J2EE Security Guide or
Java Authentication and Authorization Service Sample.


Restart the Middle Tier
-----------------------

The middle tier must be restarted in order for the changes to the formsweb.cfg
file to take effect. The commands to restart the middle tier: 

1) /bin/emctl stop iasconsole
2) /opmn/bin/opmnctl stopall
3) /bin/emctl start iasconsole
4) /opmn/bin/opmnctl startall

Enabling roles in Oracle Forms

create or replace view FRM50_ENABLED_ROLES as
select urp.granted_role role,
sum(distinct decode(rrp.granted_role,
   'ORAFORMS$OSC',2,
   'ORAFORMS$BGM',4,
   'ORAFORMS$DBG',1,0)) flag
from  sys.user_role_privs urp, role_role_privs rrp
where urp.granted_role = rrp.role (+)
  and urp.granted_role not like 'ORAFORMS$%'
group by urp.granted_role;

create public synonym FRM50_ENABLED_ROLES for system.FRM50_ENABLED_ROLES;

create role ORAFORMS$OSC;
create role ORAFORMS$DBG;
create role ORAFORMS$BGM; 

grant SELECT on FRM50_ENABLED_ROLES to public;

How to close the parent browser window when running Oracle Forms?

When a forms application is web deployed the parent browser window remains opens after exit_form. Is it possible to close the browser window programatically?

Oracle Forms does not provide any default functionality (e.g a built-in, package procedure or function, applet parameter etc) to close the parent browser window.

It is possible to create a work-around using non-Oracle javascript:

(SEE Special chapter for Internet Explorer version 7 ( IE7))

A: Closing the browser window using javascript called via WEB.SHOW_DOCUMENT.

1. Create a html file with the following code e.g.close.html for Internet Explorer:

<html>
<body onload="closeit()">
<script>
function closeit()
{
window.close();
}
</script>
</body>
</html>

2. Place in this html in a directory which can be accessed via a Oracle HTTP Server virtual directory / alias 

3. In the Forms module add the code  

WEB.SHOW_DOCUMENT('http://machinename.domain:portno/mydirectory/close.html','_self');

in a Post-FORM Trigger.

When the form is then run over the web, the trigger will fire calling the close.html which will in turn close the parent browser window.

B: Simulate Oracle Forms separateFrame=True using javascript

Create a HTML file with the following code:

<HTML>
<HEAD>
<TITLE>My HTML</TITLE>
<SCRIPT>
function my_function()
{
    // Replace Oracle.com URL below with typical Forms Servlet URL
window.open("http://machinename.domain:portno/forms/frmservlet?form=test.fmx","_blank","menubar=0, location=0,toolbar=0,personalbar=0,status=0,scrollbars=1
, resizable=1,width=700,height=550");
    
    // These lines close the first browser window after link is clicked.
    
    win = top;
    win.opener = top;
    win.close ();

}

</SCRIPT>
</HEAD>

<body onload="javascript:my_function">
<p align="center"><img border="0" 
src="http://www.oracle.com/admin/images/oralogo.gif" width="175" height="28">
<br>
<p align="center"><a href="javascript:my_function()">
Click me to run the application.</a> </p>
</body>

</HTML>

When this HTML page is run , it will have a link to launch the forms application. The application will display in a separate browser window (separateFrame=false). The javascript will, however, have removed the parent browser menu, toolbar, status bar and scrollbars. This makes it appear to the 

end user as though the forms application is running in it's own 'MDI' window.

Important Notes:

a. If separateFrame is set to true|yes in a config or default section of the formsweb.cfg it needs to be changed to separateFrame=false|no. Otherwise the result will still be the 'appearance' of a second (parent) browser window containing a black / grey rectangle.

b. When copying and pasting the sample code into a text file, remove any carriage returns which might result in the window.open line. Otherwise a javascript error will occur when clicking on the link to launch the forms application

c. FOR XP servicepack 2 users:

   SP2 has tighter security and shows the message despite the opener=top

   To avoid the message:

   1) in the HTMLbeforeForm parameter in formsweb.cfg the line:

     

   2) adding a "synchronize;" in the POST_FORM trigger before the 

      web.show_document

Source : Oracle Metalink Note 201481.1